/**
 * 
 */
package com.td.framework.webmanager.dlyh.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;
import java.util.ResourceBundle;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.td.framework.common.Constant;
import com.td.framework.utils.DateUtils;
import com.td.framework.webmanager.common.MD5;
import com.td.framework.webmanager.dlyh.dao.DlyhDao;
import com.td.framework.webmanager.domain.AppsysDlyh;
import com.td.framework.webmanager.domain.AppsysDlyhBean;
import com.td.framework.webmanager.domain.AppsysQxfwjlgx;
import com.td.framework.webmanager.domain.AppsysYmgnan;
import com.td.framework.webmanager.login.UserInfo;
import com.td.framework.webmanager.login.UserInfoQxxzzdInfo;

/**
 * 
 * Title:用户信息数据层实现类 Description:实现数据层用户信息处理方法 Copyright: Copyright (c) 2008
 * Company xx能源软件有限公司
 * 
 * @author zjp
 * @version 1.0
 */
public class DlyhDaoImpl extends HibernateDaoSupport implements DlyhDao {
	private static final Log log = LogFactory.getLog(DlyhDaoImpl.class);
	private boolean delStatus;// 删除记录标志 true:彻底删除；false:象征性删除;
	private String sql = "";// sql语句;
	
	/**
	 * @toDo 检测用户时候是否存在
	 * @return String
	 */
	public String checkUser(String yhm,String mima){
		ResourceBundle bundle = ResourceBundle.getBundle("jdbc",Locale.CHINA);
		String userName =bundle.getString("superUserName");
		String password = bundle.getString("superPassWord");
		String returnStr = "true";
		if(userName.equals(yhm)&& password.equals(mima)){
	    	  return "true";
		}else{
			MD5 md5 =new MD5();
		    mima = md5.getMD5ofStr(mima);		
		    sql = "from AppsysDlyh t where t.yhm='"+yhm+"'and mima='"+mima+"'";
		    int size = this.getHibernateTemplate().find(sql).size();
		    if(size<=0){
		       returnStr="false";
		    }
	   }
		return returnStr;
	}
	/**
	 * @toDo: 查询所用的用户信息;
	 * @return list;
	 */
	@SuppressWarnings("unchecked")
	public List findAll() {
		sql = "from com.td.framework.webmanager.domain.AppsysDlyh t where t.scbz=0 order by t.yhbh";
		List<AppsysDlyh> dlyhList = this.getHibernateTemplate().find(sql);
		return dlyhList;
	}

	/**
	 * @toDo:得到总记录数;
	 * @return rows;
	 */
	@SuppressWarnings("unchecked")
	public int getRows(String sql) {
		int rows = 0;
		Session session = this.getSession();
		SQLQuery sqlQuery = session.createSQLQuery(sql);
		List list = sqlQuery.list();
		rows = list.size();
		return rows;
	}

	/**
	 * 传入查询sql 语句返回列表数据
	 * 
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<AppsysDlyhBean> getListPageData(final String sql,
			final int pageSize, final int startRow) throws RuntimeException {
		List list = this.getHibernateTemplate().executeFind(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException {
						SQLQuery sqlQuery = session.createSQLQuery(sql);
						sqlQuery.setFirstResult(startRow);
						sqlQuery.setMaxResults(pageSize);
						AppsysDlyhBean dlyhBean = new AppsysDlyhBean();
						List<AppsysDlyhBean> list = new ArrayList<AppsysDlyhBean>();
						List ls = sqlQuery.list();
						for (Iterator it = ls.iterator(); it.hasNext();) {
							Object[] objs = (Object[]) it.next();
							dlyhBean = new AppsysDlyhBean();
							if (objs[0] != null) {
								dlyhBean.setYhbh(objs[0] + "");
							}
							if (objs[1] != null) {
								dlyhBean.setYhm(objs[1] + "");
							}
							if (objs[2] != null) {
								dlyhBean.setRybh(new Long(objs[2] + ""));
							}
							if (objs[3] != null) {
								dlyhBean.setXm(objs[3] + "");
							}
							if (objs[4] != null) {
								dlyhBean.setZw(objs[4] + "");
							}
							if (objs[5] != null) {
								dlyhBean.setBmbh(objs[5] + "");
							}
							if (objs[6] != null) {
								dlyhBean.setBmmc(objs[6] + "");
							}
							if(objs.length>8){//flt002 解决点击所属用户报错的问题，待刘卫华稍后处理 20081106
								if (objs[8] != null) {
									dlyhBean.setId(Long.parseLong(objs[8] + ""));
								}
							}
							
							list.add(dlyhBean);
						}
						return list;
					}
				});

		return (List<AppsysDlyhBean>) list;
	}

	/**
	 * @toDo:通过yhbh查询用户信息
	 * @param yhbh
	 * @return AppsysDlyh;
	 */
	@SuppressWarnings("unchecked")
	public AppsysDlyh getDlyh(String yhbh) {
		sql = " from com.td.framework.webmanager.domain.AppsysDlyh t "
				+ " where t.scbz=0 and t.yhbh='" + yhbh + "' ";
		AppsysDlyh appsysDlyh = null;
		List<AppsysDlyh> list = this.getHibernateTemplate().find(sql);
		if (!list.isEmpty()) {
			appsysDlyh = list.get(0);
		}
		return appsysDlyh;
	}

	/**
	 * @toDo:用户登录
	 * @param yhm
	 * @param mima
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public UserInfo loadUserInfo(String yhm, String mima) {
		UserInfo userInfo = null;
		Session session = this.getSession();
		Connection con = session.connection();
		Statement st = null;
		ResultSet rs = null;
		ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
		String userName =bundle.getString("superUserName");
		String password = bundle.getString("superPassWord");
		MD5 md5 = new MD5();// 实例化md5类;		
		try {
			if(yhm.equals(userName)&&mima.equals(md5.getMD5ofStr(password)))
			{
				userInfo = new UserInfo();
				userInfo.setId(new Long(-1));
				userInfo.setYhbh("-1");
				userInfo.setYhm(userName);
				userInfo.setRybh(new Long(-1));
				userInfo.setXm(userName);
				userInfo.setZw("系统管理员");
				userInfo.setBmbh("-1");
				userInfo.setBmmc("系统管理员");
				userInfo.setMima(mima);
				userInfo.setBmgdbh("-100000000");
				userInfo.setSjbhmc("");
				userInfo.setSjbmbh("");
				userInfo.setSjbmgdbh("");
				userInfo.setDlsj(new Date());
                userInfo.setXtmc(Constant.SYS_XTMC);
			}
			else
			{
				sql = "select a.id,a.yhbh ,a.yhm,b.rybh ,b.xm,b.zw,d.bmbh,d.bmmc,"
						+ "a.mima,d.bmgdbh ,e.sjbmmc,d.sjbmbh,e.sjbmgdbh " 
						+" from appsys_dlyh a ,appsys_ryjbxx b ,"
						+ "appsys_rybmgx c,appsys_bmxx d "
						+ " left join ( select e.bmbh ,e.bmmc as sjbmmc,e.bmgdbh as sjbmgdbh" 
						+" from appsys_bmxx e ) e"
						+ " on e.bmbh=d.sjbmbh "
						+ "where a.scbz=0 and "
						+ "b.rybh=a.rybh and c.rybh=b.rybh and d.bmbh = c.bmbh "
						+ " and a.yhm = '"
						+ yhm
						+ "' and a.mima = '"
						+ mima + "'";				
				st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				rs = st.executeQuery(sql);
				if (rs.next()) {
					userInfo = new UserInfo();
					userInfo.setId(rs.getLong("id"));
					userInfo.setYhbh(rs.getString("yhbh"));
					userInfo.setYhm(rs.getString("yhm"));
					userInfo.setRybh(rs.getLong("rybh"));
					userInfo.setXm(rs.getString("xm"));
					userInfo.setZw(rs.getString("zw"));
					userInfo.setBmbh(rs.getString("bmbh"));
					userInfo.setBmmc(rs.getString("bmmc"));
					userInfo.setMima(rs.getString("mima"));
					userInfo.setBmgdbh(rs.getString("bmgdbh"));
					userInfo.setSjbhmc(rs.getString("sjbmmc"));
					userInfo.setSjbmbh(rs.getString("sjbmbh"));
					userInfo.setSjbmgdbh(rs.getString("sjbmgdbh"));
					userInfo.setDlsj(new Date());
					userInfo.setXtmc(Constant.SYS_XTMC);
					//rs = null;
					/*//页面上功能按钮集合
					List<AppsysYmgnan> list = new ArrayList();
					AppsysYmgnan ymgnan = null;
					sql = "select distinct b.ymbh,b.anbh,b.anmc,b.czmb,b.mblx,b.tplj,b.tpgd," +
							"b.tpkd,b.xsfs,b.zcdku,b.zcdgs,b.ztxx,b.sjbh,b.jibie " +
							"from appsys_qxgx a , appsys_ymgnan b " +
							"where a.scbz=0 and b.ymbh=a.ymbh and b.anbh=a.lxbhz " +
							"and a.qxbz=1 and a.qxbh in ( select c.qxbh from appsys_yhzqx c  " +
							"where c.scbz=0 and c.xtbm='"+Constant.SYS_XTBM
							+"' and  (( c.yhzbh in (select d.yhbhz from appsys_yhyhzgx d  " +
							"where d.scbz=0 and d.yhbh='"+userInfo.getYhbh()+"' ) and c.flag=0 )  or " +
							"(c.yhzbh='"+userInfo.getYhbh()+"' and c.flag=1)) ) order by b.ymbh,b.anbh";
					rs = st.executeQuery(sql);
					while (rs.next()) {
						ymgnan = new AppsysYmgnan();
						ymgnan.setYmbh(rs.getString("ymbh"));
						ymgnan.setAnbh(rs.getLong("anbh"));
						ymgnan.setAnmc(rs.getString("anmc"));
						ymgnan.setCzmb(rs.getString("czmb"));
						ymgnan.setMblx(rs.getString("mblx"));
						ymgnan.setTplj(rs.getString("tplj"));
						ymgnan.setTpgd(rs.getLong("tpgd"));
						ymgnan.setTpkd(rs.getLong("tpkd"));
						ymgnan.setXsfs(rs.getLong("xsfs"));
						ymgnan.setZcdku(rs.getLong("zcdku"));
						ymgnan.setZcdgs(rs.getLong("zcdgs"));
						ymgnan.setZtxx(rs.getString("ztxx"));
						ymgnan.setSjbh(rs.getLong("sjbh"));
						ymgnan.setJibie(rs.getLong("jibie"));
						list.add(ymgnan);
					}
					userInfo.setYmgnList(list);
					rs = null;
					sql = "select distinct b.ymbh,b.zdbh,b.zdmc,b.zdywmc "
							+ "from  appsys_ymxzzd b where b.zdbh not in (select a.lxbhz "
							+ "from appsys_qxgx a where a.scbz=0 "
							+ "and a.qxbz=2 and a.qxbh in (select c.qxbh from appsys_yhzqx c "
							+ "where c.scbz=0 and c.xtbm='"+Constant.SYS_XTBM+"' " +
									" and (( c.yhzbh in ( select d.yhbhz from appsys_yhyhzgx d  "
							+ "where d.scbz=0 and d.yhbh='" + userInfo.getYhbh()
							+ "') and c.flag=0 ) " + "or (c.yhzbh='"
							+ userInfo.getYhbh() + "' and c.flag=1) )) ) "
							+ " order by b.ymbh,b.zdbh ";
					rs = st.executeQuery(sql);
					UserInfoQxxzzdInfo qxxzzdInfo = null;
					List<UserInfoQxxzzdInfo> xzzdList = new ArrayList();
					while (rs.next()) {
						qxxzzdInfo = new UserInfoQxxzzdInfo();
						qxxzzdInfo.setYmbh(rs.getString("ymbh"));
						qxxzzdInfo.setXzbh(rs.getInt("zdbh"));
						qxxzzdInfo.setZdmc(rs.getString("zdmc"));
						qxxzzdInfo.setZdywmc(rs.getString("zdywmc"));
						xzzdList.add(qxxzzdInfo);
					}
					userInfo.setXzzdList(xzzdList);
					rs = null;
					sql = "select distinct t.id, t.ymbh,t.zdywmc,t.xzbh,t.xztj,t.xstj,t.sjlx "
							+ "from appsys_qxfwjlgx t where t.scbz=0 and t.qxbh in ("
							+ "select c.qxbh from appsys_yhzqx c where c.scbz=0 " +
									" and c.xtbm='"+Constant.SYS_XTBM+"' and ( "
							+ "(c.yhzbh in (select d.yhbhz from appsys_yhyhzgx d  "
							+ "where d.scbz=0 and d.yhbh='"
							+ userInfo.getYhbh()
							+ "' ) and c.flag=0 ) "
							+ "or (c.yhzbh='"
							+ userInfo.getYhbh()
							+ "' and c.flag=1)) ) order by t.ymbh";
					List<AppsysQxfwjlgx> fwjlgxList = new ArrayList();
					AppsysQxfwjlgx fwjlgx = null;
					rs = st.executeQuery(sql);
					while (rs.next()) {
						fwjlgx = new AppsysQxfwjlgx();
						fwjlgx.setId(rs.getLong("id"));
						fwjlgx.setYmbh(rs.getString("ymbh"));
						fwjlgx.setZdywmc(rs.getString("zdywmc"));
						fwjlgx.setXzbh(rs.getLong("xzbh"));
						fwjlgx.setXztj(rs.getString("xztj"));
						fwjlgx.setXstj(rs.getString("xstj"));
						fwjlgx.setSjlx(rs.getLong("sjlx"));
						fwjlgxList.add(fwjlgx);
					}
					userInfo.setFwjlgxList(fwjlgxList);*/
					rs.close();
					st.close();
				}
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.error("Exception： DlyhDaoImpl+loadUserInfo", e);
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					log.error("Exception： DlyhDaoImpl+loadUserInfo", e);
				}
			}
		}
		////session.close();
		return userInfo;
	}

	/**
	 * @toDo:保存用户信息;
	 * @param appsysDlyh
	 * @return boolean;
	 */
	public boolean addDlyh(AppsysDlyh appsysDlyh) {
		boolean flag = false;
		try {
			MD5 md5 = new MD5();// 实例化md5类;
			Session session = getSession();
			//Transaction t = session.beginTransaction();
			appsysDlyh.setMima(md5.getMD5ofStr(appsysDlyh.getMima()));
			appsysDlyh.setScbz(new Long(0));
			if (appsysDlyh.getCjr() == null) {
				appsysDlyh.setCjr("admin");
			}
			appsysDlyh.setCjrq(DateUtils.currentDate());
			appsysDlyh.setYhbh(getNextYhbh(appsysDlyh.getRybh(), session));
			session.save(appsysDlyh);
			//t.commit();
			//session.close();
			flag = true;
		} catch (RuntimeException re) {
			flag = false;
			log.error("Exception： DlyhDaoImpl+addDlyh", re);
			throw re;
		}
		return flag;

	}

	/**
	 * @toDo:产生用户编号; 用户编码由10位部门编号+4位流水号组成;
	 * @param bmbh
	 * @param session
	 * @return yhbh;
	 */
	public String getNextYhbh(Long rybh, Session session) {
		String yhbh = "";// 用户编号;
		sql = "select max(t.yhbh) from com.td.framework.webmanager.domain.AppsysDlyh t ,"
				+ "com.td.framework.webmanager.domain.AppsysBmxx a, "
				+ "com.td.framework.webmanager.domain.AppsysRybmgx b "
				+ "where a.bmbh=b.bmbh and b.rybh="
				+ rybh
				+ " and substr(t.yhbh,0,10)=a.bmgdbh " + " order by t.yhbh ";
		yhbh = (String) session.createQuery(sql).uniqueResult();
		// 首次产生用户编号;
		if (yhbh == null) {
			sql = "select t.bmgdbh from com.td.framework.webmanager.domain.AppsysBmxx t,"
					+ " com.td.framework.webmanager.domain.AppsysRybmgx b "
					+ " where t.bmbh=b.bmbh and b.rybh=" + rybh;
			yhbh = session.createQuery(sql).uniqueResult() + "0001";
		}
		// 产生下一个编号;
		else {
			String num = (Integer.parseInt(yhbh.substring(10)) + 1) + "";
			for (int i = num.length(); i < 4; i++) {
				num = "0" + num;
			}
			yhbh = yhbh.substring(0, 10) + num;
		}
		return yhbh;
	}

	/**
	 * @toDo:更新用户信息;
	 * @param appsysDlyh
	 * @param editFlag
	 * @return boolean;
	 */
	public boolean updateDlyh(AppsysDlyh appsysDlyh, int editFlag) {
		boolean flag = false;
		try {
			if (editFlag == 1) {
				MD5 md5 = new MD5();
				appsysDlyh.setMima(md5.getMD5ofStr(appsysDlyh.getMima()));
			}
			this.getHibernateTemplate().update(appsysDlyh);
			flag = true;
		} catch (RuntimeException re) {
			flag = false;
			re.printStackTrace();
			log.error("Exception： DlyhDaoImpl+updateDlyh", re);
			throw re;
		}
		return flag;
	}
	/**
	 * @toDo:密码还原
	 * @param yhbh
	 * @return flag
	 */
	public boolean initMima(String yhbh)
	{
		boolean flag = false;
		Session session = this.getSession();
		Connection con = session.connection();
		try {
			MD5 md5 = new MD5();
			String mima = md5.getMD5ofStr("123456");
			sql = "update appsys_dlyh t set t.mima ='"+mima+"' where t.yhbh = '"+yhbh+"'";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.executeUpdate();
			pstmt.close();
			flag = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			flag = false;
			log.error("Exception： DlyhDaoImpl+initMima", e);
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					flag = false;
					log.error("Exception： DlyhDaoImpl+initMima", e);
				}
			}
			//session.close();
		}
		return flag;
	}
	/**
	 * @toDo:删除用户信息;
	 * @param yhbhs
	 * @return boolean;
	 */
	public boolean deleteDlyh(String yhbhs) {
		boolean flag = false;
		sql = " update appsys_dlyh t set t.scbz=1 where 1=1 and instr(',"+yhbhs+",',','||t.yhbh||',')  ";
		if (delStatus) {
			sql = " delete appsys_dlyh t where 1=1 and  instr(',"+yhbhs+",',','||t.yhbh||',')";
		}
		Session session = this.getSession();
		Connection con = session.connection();
		try {
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.executeUpdate();
			pstmt.close();
			flag = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			flag = false;
			log.error("Exception： DlyhDaoImpl+deleteDlyh", e);
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					flag = false;
					log.error("Exception： DlyhDaoImpl+deleteDlyh", e);
				}
			}
			//session.close();
		}
		return flag;
	}

	/**
	 * @toDo:验证用户名是否存在;
	 * @param yhm
	 * @param yhbh
	 * @return boolean;
	 */
	@SuppressWarnings("unchecked")
	public boolean checkYhm(String yhm, String yhbh) {
		boolean flag = false;
		sql = "from com.td.framework.webmanager.domain.AppsysDlyh t "
				+ "where t.yhm='" + yhm.trim() + "' order by t.yhbh";
		if (!yhbh.trim().equals("") && !yhbh.trim().equals("null")) {
			sql = "from com.td.framework.webmanager.domain.AppsysDlyh t "
					+ "where t.yhm='" + yhm.trim() + "' and t.yhbh != '"
					+ yhbh.trim() + "' order by t.yhbh";
		}
		List<AppsysDlyh> list = this.getHibernateTemplate().find(sql);
		if (!list.isEmpty()) {
			flag = true;
		}
		return flag;
	}

	/**
	 * @toDo:get删除记录标志 ;
	 * @return the delStatus
	 */
	public boolean isDelStatus() {
		return delStatus;
	}

	/**
	 * @toDo:set删除记录标志 ;
	 * @param delStatus
	 *            the delStatus to set
	 */
	public void setDelStatus(boolean delStatus) {
		this.delStatus = delStatus;
	}
}
